﻿using System;

using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace IFD2
{
    public partial class AirlineDailySaleReport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            lblError.Text = "";
            if (!IsPostBack)
            {
                if (Session["ACL"].ToString().IndexOf("IC2") < 0)
                {
                    Response.Redirect("unauthorized.aspx");
                    return;
                }

            }
        }

        protected void btnSaleRpt_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime.Parse(txtDate1.Text);
                //DateTime.Parse(txtDate2.Text);
            }
            catch
            {
                lblError.Text = "Invalid date format!";
                return;
            }

            //Response.Redirect("airline_sale_report.aspx?date1=" + txtDate1.Text + "&date2=" + txtDate2.Text);
            //return;

            StringBuilder strOut = new StringBuilder();
            DataTable dtRet = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlParameter para;
            int ret_code;
            strOut.Append("PAY_AIRLINE,PAY_OFFICE,PAY_TYPE,PAY_CURRENCY,ROE,FARE_CURRENCY,FINAL_PRICE,FINAL_SURCHARGES,TOTAL_PAY,AGENT_RATE,BK_ID,PAX_SN,TRIP_SN,AIRLINE,FARE_CODE,FARE_CABIN,FARE_CLASS,FLT_NO,FLT_DATE,ORI_CITY,DES_CITY,VIRTUAL,CABIN,CLASS,AGE_TYPE\r\n");
            cmd.CommandText = "bll_airline_get_sale_report";
            para = cmd.Parameters.AddWithValue("@airline", Session["AIRLINE"].ToString());
            para = cmd.Parameters.AddWithValue("@date1", txtDate1.Text);
            //para = cmd.Parameters.AddWithValue("@date2", txtDate2.Text);
            ret_code = TinyDAL.Exec(ref cmd, ref dtRet);
            if (dtRet.Rows.Count == 0)
            {
                lblError.Text = "No record, or the date is not in latest 3 days!";
                return;
            }

            foreach (DataRow row in dtRet.Rows)
            {
                strOut.Append(row["pay_airline"].ToString() + ",");
                strOut.Append(row["pay_office"].ToString() + ",");
                strOut.Append(row["pay_type"].ToString() + ",");
                strOut.Append(row["pay_currency"].ToString() + ",");
                strOut.Append(row["fare_roe"].ToString() + ",");
                //strOut.Append(row["tax_roe"].ToString() + ",");
                strOut.Append(row["fare_currency"].ToString() + ",");
                strOut.Append(row["final_price"].ToString() + ",");
                strOut.Append(row["final_surcharges"].ToString() + ",");
                //strOut.Append(row["tax_currency"].ToString() + ",");
                //strOut.Append(row["final_taxes"].ToString() + ",");
                strOut.Append(row["total_pay"].ToString() + ",");
                strOut.Append(row["agent_rate"].ToString() + ",");
                strOut.Append(row["bk_id"].ToString() + ",");
                strOut.Append(row["pax_sn"].ToString() + ",");
                strOut.Append(row["trip_sn"].ToString() + ",");
                strOut.Append(row["airline"].ToString() + ",");
                strOut.Append(row["fare_code"].ToString() + ",");
                strOut.Append(row["fare_cabin"].ToString() + ",");
                strOut.Append(row["fare_class"].ToString() + ",");
                strOut.Append(row["flt_no"].ToString() + ",");
                strOut.Append(row["flt_date"].ToString() + ",");
                strOut.Append(row["ori_city"].ToString() + ",");
                strOut.Append(row["des_city"].ToString() + ",");
                strOut.Append(row["virtual"].ToString() + ",");
                strOut.Append(row["cabin"].ToString() + ",");
                strOut.Append(row["class"].ToString() + ",");
                strOut.Append(row["age_type"].ToString() + "\r\n");
            }

            Response.AddHeader("Content-Disposition", "attachment; filename=airline_sale.csv");
            Response.ContentType = "application/ms-excel";
            Response.ContentEncoding = System.Text.Encoding.UTF8;//.GetEncoding("GB2312");
            Response.Write(strOut.ToString());
            //Response.Flush(); //立即传输

            Response.End();
            //HttpContext.Current.ApplicationInstance.CompleteRequest();
            //lblError.Text = "Please receive the output file.";
            //Response.Redirect("airline_balance_report.aspx");
        }
        protected void btnRefundRpt_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime.Parse(txtDate1.Text);
                //DateTime.Parse(txtDate2.Text);
            }
            catch
            {
                lblError.Text = "Invalid date format!";
                return;
            }

            //Response.Redirect("airline_refund_report.aspx?date1=" + txtDate1.Text + "&date2=" + txtDate2.Text);
            //return;

            StringBuilder strOut = new StringBuilder();
            DataTable dtRet = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlParameter para;
            int ret_code;
            strOut.Append("AIRLINE,REFUND_AIRLINE,REFUND_OFFICE,REFUND_MGR,REFUND_USER,REFUND_TIME,PAY_CURRENCY,ROE,REFUND_TRIP_FARE,CANCEL_TIME,FARE_CODE,FARE_CABIN,FARE_CLASS,FARE_CURRENCY,FINAL_PRICE,FINAL_SURCHARGES,TOTAL_PAY,AGENT_RATE,BK_ID,PAX_SN,TRIP_SN,FARE_CODE,FLT_NO,FLT_DATE,ORI_CITY,DES_CITY,VIRTUAL,AGE_TYPE\r\n");
            cmd.CommandText = "bll_airline_get_refund_report";
            para = cmd.Parameters.AddWithValue("@airline", Session["AIRLINE"].ToString());
            para = cmd.Parameters.AddWithValue("@date1", txtDate1.Text);
            //para = cmd.Parameters.AddWithValue("@date2", txtDate2.Text);
            ret_code = TinyDAL.Exec(ref cmd, ref dtRet);
            if (dtRet.Rows.Count == 0)
            {
                lblError.Text = "No record, or the date is not in latest 3 days!";
                return;
            }
            foreach (DataRow row in dtRet.Rows)
            {
                strOut.Append(row["airline"].ToString() + ",");
                strOut.Append(row["refund_airline"].ToString() + ",");
                strOut.Append(row["refund_office"].ToString() + ",");
                strOut.Append(row["refund_mgr"].ToString() + ",");
                strOut.Append(row["refund_user"].ToString() + ",");
                strOut.Append(row["refund_time"].ToString() + ",");
                strOut.Append(row["pay_currency"].ToString() + ",");
                strOut.Append(row["fare_roe"].ToString() + ",");
                strOut.Append(row["refund_trip_fare"].ToString() + ",");
                strOut.Append(row["cancel_time"].ToString() + ",");
                strOut.Append(row["fare_code"].ToString() + ",");
                strOut.Append(row["fare_currency"].ToString() + ",");
                strOut.Append(row["final_price"].ToString() + ",");
                strOut.Append(row["final_surcharges"].ToString() + ",");
                //strOut.Append(row["tax_currency"].ToString() + ",");
                //strOut.Append(row["final_taxes"].ToString() + ",");
                strOut.Append(row["total_pay"].ToString() + ",");
                strOut.Append(row["agent_rate"].ToString() + ",");
                strOut.Append(row["bk_id"].ToString() + ",");
                strOut.Append(row["pax_sn"].ToString() + ",");
                strOut.Append(row["trip_sn"].ToString() + ",");
                strOut.Append(row["fare_code"].ToString() + ",");
                strOut.Append(row["fare_cabin"].ToString() + ",");
                strOut.Append(row["fare_class"].ToString() + ",");
                strOut.Append(row["flt_no"].ToString() + ",");
                strOut.Append(row["flt_date"].ToString() + ",");
                strOut.Append(row["ori_city"].ToString() + ",");
                strOut.Append(row["des_city"].ToString() + ",");
                strOut.Append(row["virtual"].ToString() + ",");
                strOut.Append(row["age_type"].ToString() + "\r\n");
            }

            Response.AddHeader("Content-Disposition", "attachment; filename=airline_refund.csv");
            Response.ContentType = "application/ms-excel";
            Response.ContentEncoding = System.Text.Encoding.UTF8;//.GetEncoding("GB2312");
            Response.Write(strOut.ToString());
            Response.End();
        }
        protected void btnMiscSale_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime.Parse(txtDate1.Text);
                //DateTime.Parse(txtDate2.Text);
            }
            catch
            {
                lblError.Text = "Invalid date format!";
                return;
            }

            //Response.Redirect("airline_misc_sale_report.aspx?date1=" + txtDate1.Text + "&date2=" + txtDate2.Text);
            //return;

            StringBuilder strOut = new StringBuilder();
            DataTable dtRet = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlParameter para;
            int ret_code;
            strOut.Append("SELL_AIRLINE,SELL_OFFICE,PAY_TIME,EMD_AIRLINE,EMD_ID,EMD_NAME,CURRENCY,PRICE,QUANTITY,PAY_TYPE,PAY_CURRENCY,EXCH_RATE,PAY_TOTAL,TRIP_SN,STATUS,BK_ID,PAX_SN,FAMILY_NAME,NAME,FAMILY_NAME_EN,NAME_EN,AGE_TYPE,FLT_NO,FLT_DATE,ORI_AIRPORT,DES_AIRPORT,AIRLINE,VIRTUAL,CABIN,CLASS\r\n");
            cmd.CommandText = "bll_airline_get_emd_sale_report";
            para = cmd.Parameters.AddWithValue("@airline", Session["AIRLINE"].ToString());
            para = cmd.Parameters.AddWithValue("@date1", txtDate1.Text);
            para = cmd.Parameters.AddWithValue("@lang", Session["LANG"].ToString());
            ret_code = TinyDAL.Exec(ref cmd, ref dtRet);
            if (dtRet.Rows.Count == 0)
            {
                lblError.Text = "No record, or the date is not in latest 3 days!";
                return;
            }
            foreach (DataRow row in dtRet.Rows)
            {
                strOut.Append(row["sell_airline"].ToString() + ",");
                strOut.Append(row["sell_office"].ToString() + ",");
                strOut.Append(row["pay_time"].ToString() + ",");
                strOut.Append(row["emd_airline"].ToString() + ",");
                strOut.Append(row["emd_id"].ToString() + ",");
                strOut.Append(row["emd_name"].ToString() + ",");
                strOut.Append(row["currency"].ToString() + ",");
                strOut.Append(row["price"].ToString() + ",");
                strOut.Append(row["quantity"].ToString() + ",");
                strOut.Append(row["pay_type"].ToString() + ",");
                strOut.Append(row["pay_currency"].ToString() + ",");
                strOut.Append(row["exch_rate"].ToString() + ",");
                strOut.Append(row["pay_total"].ToString() + ",");
                strOut.Append(row["trip_sn"].ToString() + ",");
                strOut.Append(row["status"].ToString() + ",");
                strOut.Append(row["bk_id"].ToString() + ",");
                strOut.Append(row["pax_sn"].ToString() + ",");
                strOut.Append(row["family_name"].ToString() + ",");
                strOut.Append(row["name"].ToString() + ",");
                strOut.Append(row["family_name_en"].ToString() + ",");
                strOut.Append(row["name_en"].ToString() + ",");
                strOut.Append(row["age_type"].ToString() + ",");
                strOut.Append(row["op_flt_no"].ToString() + ",");
                strOut.Append(row["flt_date"].ToString() + ",");
                strOut.Append(row["ori_airport"].ToString() + ",");
                strOut.Append(row["des_airport"].ToString() + ",");
                strOut.Append(row["airline"].ToString() + ",");
                strOut.Append(row["virtual"].ToString() + ",");
                strOut.Append(row["cabin"].ToString() + ",");
                strOut.Append(row["class"].ToString() + "\r\n");
            }

            Response.AddHeader("Content-Disposition", "attachment; filename=airline_msic_sale.csv");
            //Response.AddHeader("Content-type", "text/csv;charset=UTF-16LE");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;//设为Default后Excel可正常打开，但用ANSI编码？
            //输出前加入BOM头，否则Excel打开中文会乱码
            string strbuf;
            strbuf = strOut.ToString();
            UTF8Encoding utf8BOM = new UTF8Encoding(true); // 指定encoding实例encoderShouldEmitUTF8Identifier
            int byteCount = utf8BOM.GetByteCount(strbuf);
            byte[] prefix = utf8BOM.GetPreamble(); // 取出UTF-8的BOM字节
            byte[] output = new byte[prefix.Length + byteCount]; // 声明输出的字节数组，注意长度
            System.Buffer.BlockCopy(prefix, 0, output, 0, prefix.Length); // 复制BOM
            utf8BOM.GetBytes(strbuf.ToCharArray(), 0, strbuf.Length, output, prefix.Length); // 写入正文
            //Response.Write(strOut.ToString());
            Response.BinaryWrite(output);
            Response.End();
        }
        protected void btnMiscRefund_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime.Parse(txtDate1.Text);
                //DateTime.Parse(txtDate2.Text);
            }
            catch
            {
                lblError.Text = "Invalid date format!";
                return;
            }

            //Response.Redirect("airline_misc_refund_report.aspx?date1=" + txtDate1.Text + "&date2=" + txtDate2.Text);
            //return;

            StringBuilder strOut = new StringBuilder();
            DataTable dtRet = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlParameter para;
            int ret_code;
            strOut.Append("REFUND_AIRLINE,REFUND_OFFICE,REFUND_TIME,SELL_AIRLINE,SELL_OFFICE,PAY_TIME,EMD_AIRLINE,EMD_ID,EMD_NAME,CURRENCY,PRICE,QUANTITY,PAY_TYPE,PAY_CURRENCY,EXCH_RATE,PAY_TOTAL,TRIP_SN,STATUS,BK_ID,PAX_SN,FAMILY_NAME,NAME,FAMILY_NAME_EN,NAME_EN,AGE_TYPE,FLT_NO,FLT_DATE,ORI_AIRPORT,DES_AIRPORT,AIRLINE,VIRTUAL,CABIN,CLASS\r\n");
            cmd.CommandText = "bll_airline_get_emd_refund_report";
            para = cmd.Parameters.AddWithValue("@airline", Session["AIRLINE"].ToString());
            para = cmd.Parameters.AddWithValue("@date1", txtDate1.Text);
            para = cmd.Parameters.AddWithValue("@lang", Session["LANG"].ToString());
            //para = cmd.Parameters.AddWithValue("@date2", txtDate2.Text);
            ret_code = TinyDAL.Exec(ref cmd, ref dtRet);
            foreach (DataRow row in dtRet.Rows)
            {
                strOut.Append(row["refund_airline"].ToString() + ",");
                strOut.Append(row["refund_office"].ToString() + ",");
                strOut.Append(row["refund_time"].ToString() + ",");
                strOut.Append(row["sell_airline"].ToString() + ",");
                strOut.Append(row["sell_office"].ToString() + ",");
                strOut.Append(row["pay_time"].ToString() + ",");
                strOut.Append(row["emd_airline"].ToString() + ",");
                strOut.Append(row["emd_id"].ToString() + ",");
                strOut.Append(row["emd_name"].ToString() + ",");
                strOut.Append(row["currency"].ToString() + ",");
                strOut.Append(row["price"].ToString() + ",");
                strOut.Append(row["quantity"].ToString() + ",");
                strOut.Append(row["pay_type"].ToString() + ",");
                strOut.Append(row["pay_currency"].ToString() + ",");
                strOut.Append(row["exch_rate"].ToString() + ",");
                strOut.Append(row["pay_total"].ToString() + ",");
                strOut.Append(row["trip_sn"].ToString() + ",");
                strOut.Append(row["status"].ToString() + ",");
                strOut.Append(row["bk_id"].ToString() + ",");
                strOut.Append(row["pax_sn"].ToString() + ",");
                strOut.Append(row["family_name"].ToString() + ",");
                strOut.Append(row["name"].ToString() + ",");
                strOut.Append(row["family_name_en"].ToString() + ",");
                strOut.Append(row["name_en"].ToString() + ",");
                strOut.Append(row["age_type"].ToString() + ",");
                strOut.Append(row["op_flt_no"].ToString() + ",");
                strOut.Append(row["flt_date"].ToString() + ",");
                strOut.Append(row["ori_airport"].ToString() + ",");
                strOut.Append(row["des_airport"].ToString() + ",");
                strOut.Append(row["airline"].ToString() + ",");
                strOut.Append(row["virtual"].ToString() + ",");
                strOut.Append(row["cabin"].ToString() + ",");
                strOut.Append(row["class"].ToString() + "\r\n");
            }

            Response.AddHeader("Content-Disposition", "attachment; filename=airline_misc_refund.csv");
            //Response.AddHeader("Content-type", "text/csv;charset=UTF-16LE");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;//设为Default后Excel可正常打开，但用ANSI编码？
            //输出前加入BOM头，否则Excel打开中文会乱码
            string strbuf;
            strbuf = strOut.ToString();
            UTF8Encoding utf8BOM = new UTF8Encoding(true); // 指定encoding实例encoderShouldEmitUTF8Identifier
            int byteCount = utf8BOM.GetByteCount(strbuf);
            byte[] prefix = utf8BOM.GetPreamble(); // 取出UTF-8的BOM字节
            byte[] output = new byte[prefix.Length + byteCount]; // 声明输出的字节数组，注意长度
            System.Buffer.BlockCopy(prefix, 0, output, 0, prefix.Length); // 复制BOM
            utf8BOM.GetBytes(strbuf.ToCharArray(), 0, strbuf.Length, output, prefix.Length); // 写入正文
            //Response.Write(strOut.ToString());
            Response.BinaryWrite(output);
            Response.End();
        }
    }
}